package com.ywt.db;

import java.util.Calendar;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class ConDbHelper {
	
	private static final String DATABASE_NAME = "ywtdb";
	private static final int DATABASE_VERSION = 1;

	private final Context mCtx; 
	
	private DatabaseHelper mDbHelper;
	private SQLiteDatabase mDb;

	private static final String TABLE_CONFIG = "config";
	

	public static final String KEY_ROWID = "_id";
	public static final String KEY_area_code = "area_code";
	public static final String KEY_channel_code = "channel_code";
	public static final String KEY_newstype_code = "newstype_code";
	public static final String KEY_newtype_code = "newtype_code";
	public static final String KEY_VER_CODE = "version_code";
	public static final String KEY_VER_PATH = "version_path";
	
	private static final String TABLE_AREA = "areas";
	
	public static final String KEY_Area_ID="Area_ID";
	public static final String KEY_Area_Name="Area_Name";
	public static final String KEY_Area_Code="Area_Code";
	public static final String KEY_Parent_ID="Parent_ID";
	
	private static final String TABLE_channels = "channels";
	
	public static final String KEY_Channel_ID="Channel_ID";
	public static final String KEY_Channel_Name="Channel_Name";
	public static final String KEY_Channel_Price="Channel_Price";
	public static final String KEY_Channel_Unit="Channel_Unit";
	public static final String KEY_Channel_Status="Channel_Status";
	public static final String KEY_DealLine_Date="DealLine_Date";
	public static final String KEY_Channel_Order="Channel_Order";
	public static final String KEY_Operator_ID="Operator_ID";
	public static final String KEY_Create_Date="Create_Date";
 
	/**
	 * Database creation sql statement
	 */
	private static final String DATABASE_CREATE = "create table "
			+ TABLE_CONFIG
			+ " (_id integer primary key autoincrement, "
			+ "area_code text not null, channel_code text not null, newstype_code text not null,newtype_code text not null,version_code text not null,version_path text not null);";
   
	
	private static final String DATABASE_CREATE2 = "create table "
			+ TABLE_AREA
			+ " (_id integer primary key autoincrement, "
			+ KEY_Area_ID+" text not null, "+KEY_Area_Name+" text not null, "+KEY_Area_Code+" text not null,"+KEY_Parent_ID+" text not null);";
 
	private static final String DATABASE_CREATE3 = "create table "
			+ TABLE_channels
			+ " (_id integer primary key autoincrement, "
			+ KEY_Channel_ID+" text not null, "+KEY_Channel_Name+" text not null, "+KEY_Channel_Price+" text not null,"+KEY_Channel_Unit+" text not null,"+KEY_Channel_Status+" text not null,"+KEY_DealLine_Date+" text not null,"+KEY_Channel_Order+" text not null,"+KEY_Operator_ID+" text not null,"+KEY_Create_Date+" text not null);";

	



	private static class DatabaseHelper extends SQLiteOpenHelper {

		DatabaseHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {

			db.execSQL(DATABASE_CREATE);
			db.execSQL(DATABASE_CREATE2);
			db.execSQL(DATABASE_CREATE3);
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			db.execSQL("DROP TABLE IF EXISTS " + TABLE_CONFIG);
			onCreate(db);
		}
	}

	/**
	 * Constructor - takes the context to allow the database to be
	 * opened/created
	 * 
	 * @param ctx
	 *            the Context within which to work
	 */
	public ConDbHelper(Context ctx) {
		this.mCtx = ctx;
	}

	/**
	 * Open the notes database. If it cannot be opened, try to create a new
	 * instance of the database. If it cannot be created, throw an exception to
	 * signal the failure
	 * 
	 * @return this (self reference, allowing this to be chained in an
	 *         initialization call)
	 * @throws SQLException
	 *             if the database could be neither opened or created
	 */
	public ConDbHelper open() throws SQLException {
		mDbHelper = new DatabaseHelper(mCtx);
		mDb = mDbHelper.getWritableDatabase();
		return this;
	}

	public void close() {
		mDbHelper.close();
	}

	 
	public long insertConfig(String area_code, String channel_code,
			String newstype_code, String newtype_code, String version_code,
			String version_path) {
//		if (checkRecord(area_code, channel_code, newstype_code, newtype_code,
//				version_code, version_path)) {
//			return -1;
//		}
		clearAll();
		ContentValues initialValues = new ContentValues();
		initialValues.put(KEY_area_code, area_code);
		initialValues.put(KEY_channel_code, channel_code);
		initialValues.put(KEY_newstype_code, newstype_code);
		initialValues.put(KEY_newtype_code, newtype_code);
		initialValues.put(KEY_VER_CODE, version_code);
		initialValues.put(KEY_VER_PATH, version_path);

		return mDb.insert(TABLE_CONFIG, null, initialValues);
	}
	
	

	public boolean checkRecord(String area_code, String channel_code,
			String newstype_code, String newtype_code, String version_code,
			String version_path) { 
		Cursor c = mDb.query(TABLE_CONFIG, new String[] { KEY_ROWID,
				KEY_area_code, KEY_channel_code, KEY_newstype_code,
				KEY_newtype_code, KEY_VER_CODE, KEY_VER_PATH }, KEY_area_code
				+ " = '" + area_code + "' and " + KEY_channel_code + " = '"
				+ channel_code + "' and " + KEY_newstype_code + " = '"
				+ newstype_code + "' and " + KEY_newtype_code + " = '"
				+ newtype_code + "' and " + KEY_area_code + " = '"
				+ version_code + "'", null, null, null, null);
		boolean rc = c.getCount() > 0;
		return rc;
	}

	
	
	public long insertAreas(String Area_ID, String Area_Name,
			String Area_Code, String Parent_ID ) {
//		 clearAllAreas();
		ContentValues initialValues = new ContentValues();
		initialValues.put(KEY_Area_ID, Area_ID);
		initialValues.put(KEY_Area_Name, Area_Name);
		initialValues.put(KEY_Area_Code, Area_Code);
		initialValues.put(KEY_Parent_ID, Parent_ID);
	 

		return mDb.insert(TABLE_AREA, null, initialValues);
	}
	
	
	 
	public long insertChannels(String Channel_ID, String Channel_Name,
			String Channel_Price, String Channel_Unit, String Channel_Status,
			String DealLine_Date,String Channel_Order,String Operator_ID  ,String Create_Date) {
//		 clearAllChannels();
		ContentValues initialValues = new ContentValues();
		initialValues.put(KEY_Channel_ID, Channel_ID);
		initialValues.put(KEY_Channel_Name, Channel_Name);
		initialValues.put(KEY_Channel_Price, Channel_Price);
		initialValues.put(KEY_Channel_Unit, Channel_Unit);
		initialValues.put(KEY_Channel_Status, Channel_Status);
		initialValues.put(KEY_DealLine_Date, DealLine_Date);
		initialValues.put(KEY_Channel_Order, Channel_Order);
		initialValues.put(KEY_Operator_ID, Operator_ID);
		initialValues.put(KEY_Create_Date, Create_Date);

		return mDb.insert(TABLE_channels, null, initialValues);
	}
	
	/**
	 * Return a Cursor over the list of all notes in the database
	 * 
	 * @return Cursor over all notes
	 */
	public Cursor fetchAllConfig() {
		Cursor cursor = mDb.query(TABLE_CONFIG, new String[] { KEY_ROWID,
				KEY_area_code, KEY_channel_code, KEY_newstype_code,
				KEY_newtype_code, KEY_VER_CODE, KEY_VER_PATH }, null, null,
				null, null, null, null);
		if (cursor != null) {
			cursor.moveToFirst();
			return cursor;
		}
		return null;
	}
 
	
	public Cursor fetchAllAreas() {
		Cursor cursor = mDb.query(TABLE_AREA, new String[] { KEY_ROWID,
				KEY_Area_ID,KEY_Area_Name,KEY_Area_Code,KEY_Parent_ID  }, null, null,
				null, null, null, null);
		if (cursor != null) {
			cursor.moveToFirst();
			return cursor;
		}
		return null;
	}
	public Cursor fetchAllChannels() {
		Cursor cursor = mDb.query(TABLE_channels, new String[] { KEY_ROWID,
				KEY_Channel_ID,KEY_Channel_Name,KEY_Channel_Price,KEY_Channel_Unit,KEY_Channel_Status,KEY_DealLine_Date,KEY_Channel_Order,KEY_Operator_ID,KEY_Create_Date }, null, null,
				null, null, null, null);
		if (cursor != null) {
			cursor.moveToFirst();
			return cursor;
		}
		return null;
	}
	

	public int clearAll() {
		int rc = mDb.delete(TABLE_CONFIG, "1", null);
		return rc;
	}
	
	public int clearAllAreas() {
		int rc = mDb.delete(TABLE_AREA, "1", null);
		return rc;
	}
	public int clearAllChannels() {
		int rc = mDb.delete(TABLE_channels, "1", null);
		return rc;
	}
	/**
	 * Delete the note with the given rowId
	 * 
	 * @param rowId
	 *            id of note to delete
	 * @return true if deleted, false otherwise
	 */
	public boolean deleteNote(long rowId) {

		return mDb.delete(TABLE_CONFIG, KEY_ROWID + "=" + rowId, null) > 0;
	}

	/**
	 * Return a Cursor positioned at the note that matches the given rowId
	 * 
	 * @param rowId
	 *            id of note to retrieve
	 * @return Cursor positioned to matching note, if found
	 * @throws SQLException
	 *             if note could not be found/retrieved
	 */
	public Cursor fetchNote(long rowId) throws SQLException {

		Cursor mCursor =

		mDb.query(true, TABLE_CONFIG, new String[] { KEY_ROWID, KEY_area_code,
				KEY_channel_code, KEY_newtype_code }, KEY_ROWID + "=" + rowId,
				null, null, null, null, null);
		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;

	}

	/**
	 * Update the note using the details provided. The note to be updated is
	 * specified using the rowId, and it is altered to use the title and body
	 * values passed in
	 * 
	 * @param rowId
	 *            id of note to update
	 * @param title
	 *            value to set note title to
	 * @param body
	 *            value to set note body to
	 * @return true if the note was successfully updated, false otherwise
	 */
	public boolean updateNote(long rowId, String title, String body) {
		ContentValues args = new ContentValues();
		args.put(KEY_area_code, title);
		args.put(KEY_channel_code, body);

		Calendar calendar = Calendar.getInstance();
		String mTime = calendar.get(Calendar.YEAR) + "年"
				+ (calendar.get(Calendar.MONTH) + 1) + "月"
				+ calendar.get(Calendar.DAY_OF_MONTH) + "日"
				+ calendar.get(Calendar.HOUR_OF_DAY) + ":"
				+ calendar.get(Calendar.MINUTE);
		args.put(KEY_newtype_code, mTime);

		return mDb.update(TABLE_CONFIG, args, KEY_ROWID + "=" + rowId, null) > 0;
	}

}
